using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace EOS.Data.Utils
{
    public class Migration
    {
        private readonly EosBase _db = new EosBase(@"Data Source=C:\Users\Max Lanovoy\Documents\Projects\EOS\EOS.Data\EosBase.sdf");

        public void Do()
        {
            const string queryString = @"SELECT [groupID], [groupName] 
                                         FROM [invGroups] 
                                         WHERE categoryID in (2,3,6,8,11,18,22,23,25)";
            IList<OverviewGroup> groups = new List<OverviewGroup>();

            using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=EVE;Integrated Security=True"))
            {
                var command = new SqlCommand(queryString, connection);
                

                connection.Open();

                var reader = command.ExecuteReader();

                if (reader == null) 
                    throw new ApplicationException("SqlDataReader was not initialized.");

                while (reader.Read())
                {
                    groups.Add(new OverviewGroup
                                   {
                                       Id = reader.GetInt16(0),
                                       Name = reader.GetString(1)
                                   });   

                }

                reader.Close();
            }

            _db.OverviewGroups.DeleteAllOnSubmit(from g in _db.OverviewGroups select g);
            _db.OverviewGroups.InsertAllOnSubmit(groups);



            _db.OverviewColumns.DeleteAllOnSubmit(from c in _db.OverviewColumns select c);
            _db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "ICON"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "DISTANCE"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "NAME"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "TYPE"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "VELOCITY"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "TAG"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "CORPORATION"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "ALLIANCE"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "FACTION"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "MILITIA"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "SIZE"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "RADIALVELOCITY"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "TRANSVERSALVELOCITY"});
			_db.OverviewColumns.InsertOnSubmit(new OverviewColumn { Id = "ANGULARVELOCITY"});

            _db.SubmitChanges();
        }
    }
}